package com.hongdee.scm.countforms.service.impl;

import com.hongdee.common.util.TokenUtil;
import com.hongdee.scm.common.service.impl.Common;
import com.hongdee.scm.countforms.dao.ItScmCountDao;
import com.hongdee.scm.countforms.entity.*;
import com.hongdee.scm.countforms.service.ITScmCountService;
import com.hongdee.scm.repertory.entity.TScmReperory;
import com.hongdee.scm.saleorder.entity.TScmSaleOrder;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 名  称:TScmCountServiceImpl
 * 类  型:class
 * 描  述:
 * 路  径:com.hongdee.scm.countforms.service.impl
 * 作  者: 张善姬
 * 日  期:2017-07-25 09:22
 */
@Service
public class TScmCountServiceImpl implements ITScmCountService {

    @Autowired
    private ItScmCountDao itScmCountDao;

    /**
     * 日报
     * @param uid
     * @param data
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map countByDay(String uid, Map data,String start,String limit) throws Exception {

        Common common = new Common();
        Boolean hasWhere = false;
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from ( select d.*,s.car_series_name car_series from t_scm_car_daily d")
            .append(" left join t_bc_car_series s on s.id = d.Car_Series_ID ");

        String cid = TokenUtil.getCompanyId(uid);
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" d.company_id = ? ");
            params.add(cid);
        }
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" d.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
        //开始日期
        if(data.get("dateStart")!=null&&!StringUtils.isEmpty(data.get("dateStart"))){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" d.query_date >= ?");
            params.add(data.get("dateStart"));
        }
        //结束日期
        if(data.get("dateEnd")!=null&&!StringUtils.isEmpty(data.get("dateEnd"))){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" d.query_date <= ?");
            params.add(data.get("dateEnd"));
        }
        sql .append(" order by d.query_date desc ) temp ");
        Map map = this.itScmCountDao.page(sql.toString(), params.toArray(), start, limit);
        return map;

    }

    /**
     * 月报
     * @param uid
     * @param data
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map countByMonth (String uid,Map data,String start,String limit) throws Exception{

        Common common = new Common();
        Boolean hasWhere = true;
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from ( select if(t.time = 0,NULL,t.time) time,t.car_series,i.stock1,o.stock2,a.stock3,s.stock4,q.stock5 from (")
           .append(" (select DISTINCT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time, c.car_series car_series")
           .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id where c.company_id = ?  ");
        String cid = TokenUtil.getCompanyId(uid);
        params.add(cid);
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" c.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
        sql.append(" ) t left join ( select IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,c.car_series car_series,count(1) as stock1")
           .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id")
           .append(" where p.invoices_type = '1'");
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" c.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
        sql.append(" group by date_format(p.strat_printTime,'%Y-%m'),c.car_series")
           .append(" ORDER BY date_format(p.strat_printTime,'%Y-%m') DESC ) i")
           .append(" on i.time = t.time and i.car_series = t.car_series left join (")
           .append(" select IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,c.car_series car_series,count(1) as stock2")
           .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id")
           .append(" where p.invoices_type = '2'");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" c.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
         sql.append(" group by date_format(p.strat_printTime,'%Y-%m'),c.car_series")
           .append(" ORDER BY date_format(p.strat_printTime,'%Y-%m') DESC) o")
           .append(" on o.time = t.time and o.car_series = t.car_series left join(")
           .append(" select IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,c.car_series car_series,count(1) as stock3")
           .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id")
           .append(" where p.invoices_type = '3'");
          hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" c.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
         sql.append(" group by date_format(p.strat_printTime,'%Y-%m'),c.car_series")
           .append(" ORDER BY date_format(p.strat_printTime,'%Y-%m') DESC) a")
           .append(" on a.time = t.time and a.car_series = t.car_series ")
           .append(" left join ( SELECT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,")
           .append(" c.car_series car_series,count(1) AS stock4")
           .append(" from ( select * from t_scm_invoice_price i where car_id not in (")
           .append(" select car_id from t_scm_invoice_price i where i.invoices_type = '5')) p")
           .append(" LEFT JOIN t_scm_car c ON p.car_id = c.id WHERE")
           .append(" (p.invoices_type = '1' or p.invoices_type ='3') and p.invoices_type <> '5'");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" c.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
        sql.append(" GROUP BY date_format(p.strat_printTime, '%Y-%m'),c.car_series")
                .append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC")
                .append(" ) s on s.time = t.time AND s.car_series = t.car_series")
                .append(" left join ( SELECT IFNULL( date_format(p.strat_printTime, '%Y-%m'),0) time,")
                .append(" c.car_series car_series,count(1) AS stock5 FROM t_scm_invoice_price p")
                .append(" LEFT JOIN t_scm_car c ON p.car_id = c.id WHERE p.invoices_type = '5'");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        //公司
        if(data.get("companyId")!=null&&!StringUtils.isEmpty(data.get("companyId"))){
            List<String> companyIdList = new ArrayList();
            companyIdList = (List<String>) data.get("companyId");

            if (data.get("companyId").toString().length() > 0) {
                hasWhere = common.appendWhereIfNeed(sql, hasWhere);
                sql.append(" c.company_id in ( ");
                for (String companyId : companyIdList) {
                    sql.append(" ? , ");
                    params.add(companyId);
                }
                sql.append(" '' ) ");

            }
        }
        sql.append(" GROUP BY date_format(p.strat_printTime, '%Y-%m'),c.car_series")
        .append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC")
        .append(" ) q ON q.time = t.time AND q.car_series = t.car_series )");

        //开始日期
        hasWhere = false;
        if(data.get("dateStart")!=null&&!StringUtils.isEmpty(data.get("dateStart"))){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            String date = data.get("dateStart").toString();
            sql.append(" t.time >= date_format(?,'%Y-%m') ");
            params.add(date);
        }
        //结束日期
        if(data.get("dateEnd")!=null&&!StringUtils.isEmpty(data.get("dateEnd"))){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            String date = data.get("dateStart").toString();
            sql.append(" t.time <= date_format(?,'%Y-%m') ");
            params.add(data.get("dateEnd"));
        }
        sql.append("order by t.time ) temp ");
        Map map = this.itScmCountDao.page(sql.toString(), params.toArray(), start, limit);
        return map;
    }

    /**
     * 日报导出
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List<TScmCount> findDayList(String uid,String dateStart,String dateEnd) throws Exception{

        List<TScmCount> tScmCounts = new ArrayList<>();
        Common common = new Common();
        Boolean hasWhere = false;
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from ( select d.*,s.car_series_name car_series from t_scm_car_daily d")
                .append(" left join t_bc_car_series s on s.id = d.Car_Series_ID ");

        String cid = TokenUtil.getCompanyId(uid);
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" d.company_id = ? ");
            params.add(cid);
        }
        //开始日期
        if(dateStart!=null&&!StringUtils.isEmpty(dateStart)){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" d.query_date >= ?");
            params.add(dateStart);
        }
        //结束日期
        if(dateEnd!=null&&!StringUtils.isEmpty(dateEnd)){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" d.query_date <= ?");
            params.add(dateEnd);
        }
        sql .append(" order by d.query_date desc ) temp ");

        List<Map<String, Object>> list1 = this.itScmCountDao.list(sql.toString(), params.toArray());
        List<TScmCount> crmCarActivityList = new ArrayList<>();
        for (Map map : list1) {
            TScmCount tScmCount = new TScmCount();

            tScmCount.setTime(String.valueOf(map.get("QueryDate")));
            if(map.get("CarSeriesID").equals("0")){
                tScmCount.setCarSeries("总和");
            }else{
                tScmCount.setCarSeries(String.valueOf(map.get("carSeries")));
            }
            tScmCount.setCostIn(String.valueOf(map.get("costIn")));
            tScmCount.setCostOut(String.valueOf(map.get("costOut")));
            tScmCount.setCostSock(String.valueOf(map.get("costStock")));
            tScmCount.setFinanceIn(String.valueOf(map.get("FinanceIn")));
            tScmCount.setFinanceOut(String.valueOf(map.get("FinanceOut")));
            tScmCount.setFinanceAll(String.valueOf(map.get("FinanceStock")));
            tScmCount.setStockIn(String.valueOf(map.get("CarIn")));
            tScmCount.setStockOut(String.valueOf(map.get("CarOut")));
            tScmCount.setStockAll(String.valueOf(map.get("CarStock")));

            tScmCounts.add(tScmCount);
        }
        return tScmCounts;
    }

    String[] excelHeader = {"时间", "车系", "财务入库", "财务入库金额",
            "财务出库", "财务出库金额", "财务库存", "财务库存金额",
            "仓储入库", "仓储出库","仓储库存"};
    @Override
    public HSSFWorkbook exportDay(List<TScmCount> list, String uid) throws Exception{

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("日报");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeader.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmCount tScmCount = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmCount.getTime());
            row.createCell(1).setCellValue(tScmCount.getCarSeries());
            row.createCell(2).setCellValue(tScmCount.getFinanceIn());
            row.createCell(3).setCellValue(tScmCount.getCostIn());
            row.createCell(4).setCellValue(tScmCount.getFinanceOut());
            row.createCell(5).setCellValue(tScmCount.getCostOut());
            row.createCell(6).setCellValue(tScmCount.getFinanceAll());
            row.createCell(7).setCellValue(tScmCount.getCostSock());
            row.createCell(8).setCellValue(tScmCount.getStockIn());
            row.createCell(9).setCellValue(tScmCount.getStockOut());
            row.createCell(8).setCellValue(tScmCount.getStockAll());
        }
        return wb;
    }

    /**
     * 月报导出
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public List<TScmMonthCount> findMonthList(String uid,String dateStart,String dateEnd) throws Exception{

        Common common = new Common();
        Boolean hasWhere = true;
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from ( select if(t.time = 0,NULL,t.time) time,t.car_series,i.stock1,o.stock2,a.stock3,s.stock4,q.stock5 from (")
                .append(" (select DISTINCT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time, c.car_series car_series")
                .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id where c.company_id = ?  ");
        String cid = TokenUtil.getCompanyId(uid);
        params.add(cid);
        sql.append(" ) t left join ( select IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,c.car_series car_series,count(1) as stock1")
                .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id")
                .append(" where p.invoices_type = '1'");
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" group by date_format(p.strat_printTime,'%Y-%m'),c.car_series")
                .append(" ORDER BY date_format(p.strat_printTime,'%Y-%m') DESC ) i")
                .append(" on i.time = t.time and i.car_series = t.car_series left join (")
                .append(" select IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,c.car_series car_series,count(1) as stock2")
                .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id")
                .append(" where p.invoices_type = '2'");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" group by date_format(p.strat_printTime,'%Y-%m'),c.car_series")
                .append(" ORDER BY date_format(p.strat_printTime,'%Y-%m') DESC) o")
                .append(" on o.time = t.time and o.car_series = t.car_series left join(")
                .append(" select IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,c.car_series car_series,count(1) as stock3")
                .append(" from t_scm_invoice_price p left join t_scm_car c on p.car_id = c.id")
                .append(" where p.invoices_type = '3'");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" group by date_format(p.strat_printTime,'%Y-%m'),c.car_series")
                .append(" ORDER BY date_format(p.strat_printTime,'%Y-%m') DESC) a")
                .append(" on a.time = t.time and a.car_series = t.car_series ")
                .append(" left join ( SELECT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,")
                .append(" c.car_series car_series,count(1) AS stock4")
                .append(" from ( select * from t_scm_invoice_price i where car_id not in (")
                .append(" select car_id from t_scm_invoice_price i where i.invoices_type = '5')) p")
                .append(" LEFT JOIN t_scm_car c ON p.car_id = c.id WHERE")
                .append(" (p.invoices_type = '1' or p.invoices_type ='3') and p.invoices_type <> '5'");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" GROUP BY date_format(p.strat_printTime, '%Y-%m'),c.car_series")
                .append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC")
                .append(" ) s on s.time = t.time AND s.car_series = t.car_series  ")
                .append(" left join ( SELECT IFNULL( date_format(p.strat_printTime, '%Y-%m'),0) time,")
                .append(" c.car_series car_series,count(1) AS stock5 FROM t_scm_invoice_price p")
                .append(" LEFT JOIN t_scm_car c ON p.car_id = c.id WHERE p.invoices_type = '5'");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" GROUP BY date_format(p.strat_printTime, '%Y-%m'),c.car_series")
                .append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC")
                .append(" ) q ON q.time = t.time AND q.car_series = t.car_series )");
        //开始日期
        hasWhere = false;
        if(dateStart!=null&&!StringUtils.isEmpty(dateStart)){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" t.time >= ? ");
            params.add(dateStart);
        }
        //结束日期
        if(dateEnd!=null&&!StringUtils.isEmpty(dateEnd)){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" t.time <= ? ");
            params.add(dateEnd);
        }
        sql.append("order by t.time ) temp ");

        List<Map<String, Object>> list1 = this.itScmCountDao.list(sql.toString(), params.toArray());

        List<TScmMonthCount> tScmMonthCounts = new ArrayList<>();
        for (Map map : list1) {
            TScmMonthCount tScmMonthCount = new TScmMonthCount();

            tScmMonthCount.setTime(String.valueOf(map.get("time")));
            tScmMonthCount.setCarSeries(String.valueOf(map.get("carSeries")));
            if(map.get("stock1")==null){
                tScmMonthCount.setZanFiance("0");
            }else{
                tScmMonthCount.setZanFiance(String.valueOf(map.get("stock1")));
            }
            if(map.get("stock2")==null){
                tScmMonthCount.setChongFiance("0");
            }else{
                tScmMonthCount.setChongFiance(String.valueOf(map.get("stock2")));
            }
            if(map.get("stock3")==null){
                tScmMonthCount.setZhengFiance("0");
            }else{
                tScmMonthCount.setZhengFiance(String.valueOf(map.get("stock3")));
            }
            if(map.get("stock4")==null){
                tScmMonthCount.setStockFiance("0");
            }else{
                tScmMonthCount.setStockFiance(String.valueOf(map.get("stock4")));
            }
            if(map.get("stock5")==null){
                tScmMonthCount.setSaleFinance("0");
            }else{
                tScmMonthCount.setSaleFinance(String.valueOf(map.get("stock5")));
            }
            tScmMonthCounts.add(tScmMonthCount);
        }
        return tScmMonthCounts;
    }

    String[] excelHeaders = {"日期", "车系名称", "暂估入库", "冲估入库", "正式入库", "财务库存","销售数量"};
    @Override
    public HSSFWorkbook exportMonth(List<TScmMonthCount> list, String uid) throws Exception{

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("月报");


        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaders.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaders[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmMonthCount tScmMonthCount = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmMonthCount.getTime());
            row.createCell(1).setCellValue(tScmMonthCount.getCarSeries());
            row.createCell(2).setCellValue(tScmMonthCount.getZanFiance());
            row.createCell(3).setCellValue(tScmMonthCount.getChongFiance());
            row.createCell(4).setCellValue(tScmMonthCount.getZhengFiance());
            row.createCell(5).setCellValue(tScmMonthCount.getStockFiance());
            row.createCell(6).setCellValue(tScmMonthCount.getSaleFinance());
        }
        return wb;
    }

    @Override
    public List<TScmMonthDetail> findMonthDetailList(String uid, String dateStart, String dateEnd) throws Exception{

        if(dateStart==null||dateStart.equals("")){
            throw new Exception("请选择导出日期");
        }
        if(dateEnd==null||dateEnd.equals("")){
            throw new Exception("请选择导出日期");
        }
        if(!dateStart.equals(dateEnd)){
            throw new Exception("请选择一个月");
        }
        String cid = TokenUtil.getCompanyId(uid);
        Common common = new Common();
        Boolean hasWhere = true;
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer();
        sql.append("select * from ( select t.time,t.car_series,t.car_model,t.vin,t.car_id,z.invoices_price zprice," +
                " substr(c.invoices_price,2,LENGTH(c.invoices_price)) cprice,r.invoices_price rprice," +
                " s.invoices_price sprice ,if(z.invoices_price is not null or z.invoices_price <>'','已暂估','') zangu," +
                " if(c.invoices_price is not null or c.invoices_price <>'','已冲估','') chonggu," +
                " if(r.invoices_price is not null or r.invoices_price <>'','已正入','') zhengru," +
                " if(s.invoices_price is not null or s.invoices_price <>'','已售','') xiaoshou," +
                " sum(if(z.invoices_price is not null or z.invoices_price <>'',1,0)) zcount," +
                " sum(if(c.invoices_price is not null or c.invoices_price <>'',1,0)) ccount," +
                " sum(if(r.invoices_price is not null or r.invoices_price <>'',1,0)) rcount," +
                " sum(if(s.invoices_price is not null or s.invoices_price <>'',1,0)) scount," +
                " sum(if(z.invoices_price is not null or z.invoices_price <>'',z.invoices_price,0)) zc," +
                " sum(if(c.invoices_price is not null or c.invoices_price <>'',substr(c.invoices_price,2,LENGTH(c.invoices_price)),0)) cc," +
                " sum(if(r.invoices_price is not null or r.invoices_price <>'',r.invoices_price,0)) rc," +
                " sum(if(s.invoices_price is not null or s.invoices_price <>'',s.invoices_price,0)) sc from (" +
                " SELECT DISTINCT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,p.car_id," +
                " c.car_series car_series,c.car_model car_model,c.vin FROM t_scm_invoice_price p" +
                " LEFT JOIN t_scm_car c ON p.car_id = c.id ");
        hasWhere = false;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" ) t left join (" +
                " SELECT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,p.car_id,p.invoices_price" +
                " FROM t_scm_invoice_price p LEFT JOIN t_scm_car c ON p.car_id = c.id" +
                " WHERE p.invoices_type = '1' ");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC ) z on t.time = z.time and t.car_id = z.car_id" +
                " left join( SELECT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,p.car_id,p.invoices_price" +
                " FROM t_scm_invoice_price p LEFT JOIN t_scm_car c ON p.car_id = c.id" +
                " WHERE p.invoices_type = '2' ");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC ) c on c.time=t.time and c.car_id = t.car_id" +
                " left join ( SELECT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,p.car_id,p.invoices_price" +
                " FROM t_scm_invoice_price p LEFT JOIN t_scm_car c ON p.car_id = c.id" +
                " WHERE p.invoices_type = '3' ");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC ) r on r.time = t.time and r.car_id = t.car_id" +
                " left join ( SELECT IFNULL(date_format(p.strat_printTime, '%Y-%m'),0) time,p.car_id,p.invoices_price" +
                " FROM t_scm_invoice_price p LEFT JOIN t_scm_car c ON p.car_id = c.id" +
                " WHERE p.invoices_type = '5' ");
        hasWhere = true;
        if(!cid.isEmpty()){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" c.company_id = ? ");
            params.add(cid);
        }
        sql.append(" ORDER BY date_format(p.strat_printTime, '%Y-%m') DESC ) s on s.time = t.time and s.car_id = t.car_id");

        //开始日期
        hasWhere = false;
        if(dateStart!=null&&!StringUtils.isEmpty(dateStart)){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" t.time >= date_format(?,'%Y-%m') ");
            params.add(dateStart);
        }
        //结束日期
        if(dateEnd!=null&&!StringUtils.isEmpty(dateEnd)){
            hasWhere = common.appendWhereIfNeed(sql, hasWhere);
            sql.append(" t.time <= date_format(?,'%Y-%m') ");
            params.add(dateEnd);
        }
        sql.append(" GROUP BY t.car_id with ROLLUP ) temp ");

        List<Map<String,Object>> list = this.itScmCountDao.list(sql.toString(),params.toArray());
        List<TScmMonthDetail> tScmMonthDetails = new ArrayList<>();
        for(int i=0;i<list.size();i++){

            Map map = list.get(i);
            TScmMonthDetail tScmMonthDetail = new TScmMonthDetail();
            tScmMonthDetail.setTime(String.valueOf(map.get("time").toString()));
            tScmMonthDetail.setCarSeries(String.valueOf(map.get("carSeries").toString()));
            tScmMonthDetail.setCarModel(String.valueOf(map.get("carModel").toString()));
            tScmMonthDetail.setVin(String.valueOf(map.get("vin").toString()));
            if(map.get("zprice")!=null&&!"".equals(map.get("zprice"))){
               tScmMonthDetail.setZanPrice(String.valueOf(map.get("zprice").toString()));
            }else{
                tScmMonthDetail.setZanPrice("0");
            }
            if(map.get("cprice")!=null&&!"".equals(map.get("cprice"))){
                tScmMonthDetail.setChongPrice(String.valueOf(map.get("cprice").toString()));
            }else{
                tScmMonthDetail.setChongPrice("0");
            }
            if(map.get("rprice")!=null&&!"".equals(map.get("rprice"))){
                tScmMonthDetail.setZhengPrice(String.valueOf(map.get("rprice").toString()));
            }else{
                tScmMonthDetail.setZhengPrice("0");
            }
            if(map.get("sprice")!=null&&!"".equals(map.get("sprice"))){
                tScmMonthDetail.setSalePrice(String.valueOf(map.get("sprice").toString()));
            }else{
                tScmMonthDetail.setSalePrice("0");
            }
            tScmMonthDetail.setZanStatus(String.valueOf(map.get("zangu").toString()));
            tScmMonthDetail.setChongStatus(String.valueOf(map.get("chonggu").toString()));
            tScmMonthDetail.setZhengStatus(String.valueOf(map.get("zhengru").toString()));
            tScmMonthDetail.setSaleStatus(String.valueOf(map.get("xiaoshou").toString()));
            //最后一行
            if(i==(list.size()-1)){
                tScmMonthDetail.setTime("汇总");
                tScmMonthDetail.setZanStatus(String.valueOf(map.get("zcount").toString()));
                tScmMonthDetail.setChongStatus(String.valueOf(map.get("ccount").toString()));
                tScmMonthDetail.setZhengStatus(String.valueOf(map.get("rcount").toString()));
                tScmMonthDetail.setSaleStatus(String.valueOf(map.get("scount").toString()));
                tScmMonthDetail.setZanPrice(String.valueOf(map.get("zc").toString()));
                tScmMonthDetail.setChongPrice(String.valueOf(map.get("cc").toString()));
                tScmMonthDetail.setZhengPrice(String.valueOf(map.get("rc").toString()));
                tScmMonthDetail.setSalePrice(String.valueOf(map.get("sc").toString()));
                tScmMonthDetail.setCarModel(null);
                tScmMonthDetail.setCarSeries(null);
                tScmMonthDetail.setVin(null);
            }
            tScmMonthDetails.add(tScmMonthDetail);
        }
        return tScmMonthDetails;
    }

    String[] excelHeaderss = {"日期", "车系名称", "车型名称", "vin", "暂估价", "冲估价","发票价","销售价",
            "暂估状态","冲估状态","正入状态","销售状态"};
    @Override
    public HSSFWorkbook exportMonthDetail(List<TScmMonthDetail> list, String uid) throws Exception{
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("月报明细");

        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderss.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderss[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一

            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmMonthDetail tScmMonthDetail = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmMonthDetail.getTime());
            row.createCell(1).setCellValue(tScmMonthDetail.getCarSeries());
            row.createCell(2).setCellValue(tScmMonthDetail.getCarModel());
            row.createCell(3).setCellValue(tScmMonthDetail.getVin());
            row.createCell(4).setCellValue(tScmMonthDetail.getZanPrice());
            row.createCell(5).setCellValue(tScmMonthDetail.getChongPrice());
            row.createCell(6).setCellValue(tScmMonthDetail.getZhengPrice());
            row.createCell(7).setCellValue(tScmMonthDetail.getSalePrice());
            row.createCell(8).setCellValue(tScmMonthDetail.getZanStatus());
            row.createCell(9).setCellValue(tScmMonthDetail.getChongStatus());
            row.createCell(10).setCellValue(tScmMonthDetail.getZhengStatus());
            row.createCell(11).setCellValue(tScmMonthDetail.getSaleStatus());
        }
        return wb;
    }

    /**
     * 月报穿透
     * @param uid
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public Map monthThrough(String uid,Map data,String start,String limit) throws Exception{

        if(data.get("carSeries")==null||"".equals(data.get("carSeries"))){
            throw new Exception("参数有误，查询无效!");
        }
        if(data.get("month")==null||"".equals(data.get("month"))){
            throw new Exception("参数有误，查询无效!");
        }
        if(data.get("type")==null||"".equals(data.get("type"))){
            throw new Exception("参数有误，查询无效!");
        }

        String type = data.get("type").toString();
        StringBuffer sql = new StringBuffer();
        List<String> params = new ArrayList<>();
        String cid = TokenUtil.getCompanyId(uid);
        sql.append("select c.vin,c.car_series,c.car_model,c.configure,p.invoices_price,p.strat_printTime " +
                "from t_scm_car c,t_scm_invoice_price p  " +
                "where p.invoices_type = ? and p.strat_printTime like concat(?,'%') " +
                "and p.car_id = c.id and c.car_series = ? and c.company_id =? order by p.strat_printTime desc");
        if(type.equals("stock1")){//暂估
          params.add("1");
        }
        if(type.equals("stock2")){//冲估
            params.add("2");
        }
        if(type.equals("stock3")){//正入
            params.add("3");
        }
        if(type.equals("stock5")){//销售
            params.add("5");
        }
        params.add(data.get("month").toString());
        params.add(data.get("carSeries").toString());
        params.add(cid);
        Map map = this.itScmCountDao.page(sql.toString(),params.toArray(),start,limit);
        return map;
    }

    /**
     * 润华汽车库存统计表
     * @param uid
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map stockCountForms(String uid,String start,String limit,String month) throws Exception{

        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select * from t_scm_stock_form where 1=1");
        if(month!=null&&!"".equals(month)){
            sql.append(" and query_month = ?");
            params.add(month);
        }
        Map map = this.itScmCountDao.page(sql.toString(),params.toArray(),start, limit);
        return map;
    }

    @Override
    public List<TScmStockForms> findStockFormsList(String month) throws Exception{

        List<TScmStockForms> tScmStockForms = new ArrayList<>();
        //获取数据
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
        sql.append("select * from t_scm_stock_form where 1=1");
        if(month!=null&&!"".equals(month)){
            sql.append(" and query_month = ?");
            params.add(month);
        }
        List<Map<String,Object>> list = this.itScmCountDao.list(sql.toString());

        for(Map<String,Object> map:list){
            TScmStockForms stockForms = new TScmStockForms();
            stockForms.setQueryMonth(String.valueOf(map.get("queryMonth").toString()));
            stockForms.setBrandName(String.valueOf(map.get("brandName").toString()));
            stockForms.setCompanyName(String.valueOf(map.get("companyName").toString()));
            stockForms.setStockPrice(String.valueOf(map.get("stockPrice").toString()));
            stockForms.setLastPrice(String.valueOf(map.get("lastPrice").toString()));
            stockForms.setNowCount(String.valueOf(map.get("nowCount").toString()));
            stockForms.setNowPrice(String.valueOf(map.get("nowPrice").toString()));
            stockForms.setOverCount(String.valueOf(map.get("overCount").toString()));
            stockForms.setOverPrice(String.valueOf(map.get("overPrice").toString()));
            stockForms.setMoreCount(String.valueOf(map.get("moreCount").toString()));
            stockForms.setMorePrice(String.valueOf(map.get("morePrice").toString()));
            stockForms.setLastYearPrice(String.valueOf(map.get("lastYearPrice").toString()));
            stockForms.setTurnDay(String.valueOf(map.get("turnDay").toString()));
            stockForms.setLastTurnDays(String.valueOf(map.get("lastTurnDays").toString()));
            stockForms.setNowYearCost(String.valueOf(map.get("nowYearCost").toString()));
            tScmStockForms.add(stockForms);
        }
        return tScmStockForms;
    }

    String[] formExcelHeaders = {"月份","品牌名称", "公司名称", "预算平均库存", "期末平均库存", "上月库存余额", "期末库存数量",
            "期末库存余额","超期库存数量", "超期库存金额","整车6个月以上数量","整车6个月以上金额","去年同期库存余额",
            "核定周转天数","本期库存周转天数","去年同期周转天数","累计出库成本"};
    @Override
    public HSSFWorkbook exportStockForm(List<TScmStockForms> list) throws Exception{

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("润华汽车库存统计表");

        //设置字体(表头)
        HSSFFont headfont = wb.createFont();
        headfont.setFontName("黑体");
        headfont.setFontHeightInPoints((short) 12);// 字体大小
        headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗

        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setFont(headfont);

        for (int i = 0; i < formExcelHeaders.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(formExcelHeaders[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);

            sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽

            //设置指定列的列宽，256 * 30这种写法是因为width参数单位是单个字符的256分之一
            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmStockForms tScmStockForms = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmStockForms.getQueryMonth());
            row.createCell(1).setCellValue(tScmStockForms.getBrandName());
            row.createCell(2).setCellValue(tScmStockForms.getCompanyName());
            row.createCell(3).setCellValue(tScmStockForms.getStockPrice());
            row.createCell(5).setCellValue(tScmStockForms.getLastPrice());
            row.createCell(6).setCellValue(tScmStockForms.getNowCount());
            row.createCell(7).setCellValue(tScmStockForms.getNowPrice());
            row.createCell(8).setCellValue(tScmStockForms.getOverCount());
            row.createCell(9).setCellValue(tScmStockForms.getOverPrice());
            row.createCell(10).setCellValue(tScmStockForms.getMoreCount());
            row.createCell(11).setCellValue(tScmStockForms.getMorePrice());
            row.createCell(12).setCellValue(tScmStockForms.getLastYearPrice());
            row.createCell(14).setCellValue(tScmStockForms.getTurnDay());
            row.createCell(15).setCellValue(tScmStockForms.getLastTurnDays());
            row.createCell(16).setCellValue(tScmStockForms.getNowYearCost());
        }
        return wb;
    }

    @Override
    public List<TScmMonthCar> findMonthCarList(String uid, Map data) throws Exception{

        List<TScmMonthCar> tScmMonthCarList = new ArrayList<>();
        List<Map<String,Object>> list = this.getMonthCar(uid,data);
        for(Map<String,Object> map:list){
            TScmMonthCar tScmMonthCar = new TScmMonthCar();
            if(map.get("stratPrintTime")!=null&&!"".equals(map.get("stratPrintTime"))){
               tScmMonthCar.setDate(String.valueOf(map.get("stratPrintTime").toString()));
            }
            if(map.get("vin")!=null&&!"".equals(map.get("vin"))){
                tScmMonthCar.setVin(String.valueOf(map.get("vin").toString()));
            }
            if(map.get("carSeries")!=null&&!"".equals(map.get("carSeries"))){
                tScmMonthCar.setCarSeries(String.valueOf(map.get("carSeries").toString()));
            }
            if(map.get("carModel")!=null&&!"".equals(map.get("carModel"))){
                tScmMonthCar.setCarModel(String.valueOf(map.get("carModel").toString()));
            }
            if(map.get("configure")!=null&&!"".equals(map.get("configure"))){
                tScmMonthCar.setCarConfigure(String.valueOf(map.get("configure").toString()));
            }
            if(map.get("invoicesPrice")!=null&&!"".equals(map.get("invoicesPrice"))){
                tScmMonthCar.setPrice(String.valueOf(map.get("invoicesPrice").toString()));
            }
            if(map.get("invoicesType")!=null&&!"".equals(map.get("invoicesType"))){
                tScmMonthCar.setType(String.valueOf(map.get("invoicesType").toString()));
            }
            tScmMonthCarList.add(tScmMonthCar);
        }
        return tScmMonthCarList;
    }

    private List<Map<String,Object>> getMonthCar(String uid, Map data) throws Exception{

        if(data.get("carSeries")==null||"".equals(data.get("carSeries"))){
            throw new Exception("参数有误，查询无效!");
        }
        if(data.get("month")==null||"".equals(data.get("month"))){
            throw new Exception("参数有误，查询无效!");
        }
        if(data.get("type")==null||"".equals(data.get("type"))){
            throw new Exception("参数有误，查询无效!");
        }

        String type = data.get("type").toString();
        StringBuffer sql = new StringBuffer();
        List<String> params = new ArrayList<>();
        String cid = TokenUtil.getCompanyId(uid);
        sql.append("select c.vin,c.car_series,c.car_model,c.configure,p.invoices_price,p.strat_printTime,p.invoices_type " +
                "from t_scm_car c,t_scm_invoice_price p  " +
                "where p.invoices_type = ? and p.strat_printTime like concat(?,'%') " +
                "and p.car_id = c.id and c.car_series = ? and c.company_id =? order by p.strat_printTime desc");
        if(type.equals("stock1")){//暂估
            params.add("1");
        }
        if(type.equals("stock2")){//冲估
            params.add("2");
        }
        if(type.equals("stock3")){//正入
            params.add("3");
        }
        if(type.equals("stock5")){//销售
            params.add("5");
        }
        params.add(data.get("month").toString());
        params.add(data.get("carSeries").toString());
        params.add(cid);
        List<Map<String,Object>> list = this.itScmCountDao.list(sql.toString(),params.toArray());
        return list;
    }

    @Override
    public XSSFWorkbook createExcel(List<TScmMonthCar> list, String uid) throws Exception{

        String[] excelHeaderRecordout = {"日期","VIN","车系名称","车型名称","配置名称","金额","状态"};

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("月报钻取明细");
        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }

        for (int i = 0; i < list.size(); i++) {
            TScmMonthCar tScmMonthCar = list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(tScmMonthCar.getDate());
            row.createCell(1).setCellValue(tScmMonthCar.getVin());
            row.createCell(2).setCellValue(tScmMonthCar.getCarSeries());
            row.createCell(3).setCellValue(tScmMonthCar.getCarModel());
            row.createCell(4).setCellValue(tScmMonthCar.getCarConfigure());
            row.createCell(5).setCellValue(tScmMonthCar.getPrice());
            switch (tScmMonthCar.getType()){
               case "1":
                   row.createCell(6).setCellValue("暂估");
                    break;
               case "2":
                   row.createCell(6).setCellValue("冲估");
                    break;
               case "3":
                   row.createCell(6).setCellValue("正入");
                    break;
               case "5":
                   row.createCell(6).setCellValue("已售");
                    break;
               default:
                    break;
            }

        }
        return wb;
    }
}
